using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using MyWeb.Common;

namespace MyWeb.Data
{
    public class SqlDataProvider
    {
        public readonly string Connectionstring = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
        public SqlConnection Con;

        public SqlDataProvider()
        {
            if (Con == null)
            {
                Con = new SqlConnection(Connectionstring);
            }
        }

        public SqlCommand Cmd { get; set; }
        public DataTable Dt { get; set; }

        public SqlConnection GetCon()
        {
            if (Con.State == ConnectionState.Closed)
            {
                Con.Open();
                return Con;
            }
            return Con;
        }

        public DataTable GetTable(string sql)
        {
            var da = new SqlDataAdapter(sql, Con);
            var dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public void ExecuteNonQuery(string sql)
        {
            Con = GetCon();
            var cmd = new SqlCommand(sql, Con);
            cmd.ExecuteNonQuery();
            Con.Close();
            cmd.Dispose();
        }

        public DataTable GetDataTable(string stoPr, params SqlParameter[] parameter)
        {
            Con = GetCon();
            Cmd = new SqlCommand(stoPr, Con);
            Dt = new DataTable();
            Cmd.CommandType = CommandType.StoredProcedure;
            Cmd.Parameters.AddRange(parameter);
            try
            {
                Dt.Load(Cmd.ExecuteReader());
            }
            catch (Exception ex)
            {
                WebMsgBox.Show(ex.Message);
            }
            finally
            {
                Con.Close();
                Cmd.Dispose();
            }
            return Dt;
        }

        public void ExecuteStoreProcedure(string stoPr, params SqlParameter[] parameter)
        {
            Con = GetCon();
            Cmd = new SqlCommand(stoPr, Con);
            Cmd.CommandType = CommandType.StoredProcedure;
            Cmd.Parameters.AddRange(parameter);
            try
            {
                Cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                WebMsgBox.Show(ex.Message); 
            }
            finally
            {
                Con.Close();
                Cmd.Dispose();
            }
        }
        public string GetTopDataCell(string nameTable, string nameSelectColumn, string orderByColumn)
        {
            string sql = string.Format(@"SELECT TOP 1 * FROM {0} ORDER BY {1} DESC", nameTable, orderByColumn);
            DataTable dtb = GetTable(sql);

            return dtb.Rows[0][nameSelectColumn].ToString();
        }
    }
}